package com.vcolco.utils;

import com.alibaba.fastjson.JSONObject;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class ExcelUtils<T> {

    private int sheetSize = 30000;

    /**
     * 当无法确定数据量大小时使用 自动判断导出文件为zip文件还是xlsx文件
     * 使用之前建议先调用getFileName接口获取文件后缀名
     *
     *@param columns 列名（字段名）
     *@param columnNames 列名（展示列表的中文名）
     *@param fileds 数据集
     *@param outputStream 输出流
     * @throws Exception
     */
    public void down(String[] columns, String[] columnNames, List<T> fileds, OutputStream outputStream) throws Exception {
        Date start = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        log.info("文件导出开始时间："+simpleDateFormat.format(start)+"需要导出数据条数为:"+fileds.size());
        simpleDown(columns, columnNames, fileds, outputStream);
        Date end = new Date();
        log.info("文件导出结束时间："+simpleDateFormat.format(end)+"耗时："+(end.getTime() - start.getTime())+"毫秒");
    }

    /**
     * 上传流 返回数据集
     * @param inputStream
     * @param columnNames
     * @param cls
     * @return
     */
    public List<T> upload(MultipartFile file, String[] columnNames, Class<T> cls, int pageIndex, int pageSize){
        byte[] fileBytes = FileUtil.getBytes(file);
        InputStream inputStream = new ByteArrayInputStream(fileBytes);
        List<T> result = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(inputStream);
        }catch (Exception e){
            throw new RuntimeException("获取文件流失败");
        }
        Sheet[] sheets = workbook.getSheets();
        ValidationUtils<T> validationUtil = new ValidationUtils<>();
//        //遍历sheet
//        for (int sheetIndex = 0 ; sheetIndex < sheets.length ; sheetIndex++){
//            //获取sheeet
//            Sheet sheet = workbook.getSheet(sheetIndex);
//            if (null == sheet) throw new RuntimeException("获取sheet"+sheetIndex+"失败");
//            //获取所有行数据
//            for (int rowNum = 1;rowNum < sheet.getRows(); rowNum++){
//                Cell[] cells = sheet.getRow(rowNum);
//                if (cells.length < columnNames.length) throw new RuntimeException("导入数据有误");
//                JSONObject ob = new JSONObject();
//                // 获取每一行 一列的数据 拼装实体
//                for (int columnIndex = 0;columnIndex < columnNames.length;columnIndex++){
//                    ob.put(columnNames[columnIndex],cells[columnIndex].getContents());
//                }
//                T data = ob.toJavaObject(cls);
//                //数据校验
//                validationUtil.validate(data);
//                result.add(data);
//            }
//        }
        int startSheet = 0;//开始shrrt
        int startNum = 0;//对应sheet的行数
        int count = 0;//当前累计数
        int startSize = (pageIndex - 1) * pageSize;
        //确定开始sheet 和开始位置
        for (int sheetIndex = 0 ; sheetIndex < sheets.length ; sheetIndex++){
            Sheet sheet = workbook.getSheet(sheetIndex);
            int sheetNum = sheet.getRows() -1;
            count = count + sheetNum;
            if (count > startSize) {
                startSheet = sheetIndex;
                startNum = pageSize * (pageIndex - 1) - count + 1 + sheetNum;
                break;
            }
        }
        if (startSize > count) return result;

        //开始遍历数据
        for (int sheetIndex = startSheet ; sheetIndex < sheets.length ; sheetIndex++){
            Sheet sheet = workbook.getSheet(sheetIndex);
            for (int rowNum = startNum;rowNum < sheet.getRows(); rowNum++){
                Cell[] cells = sheet.getRow(rowNum);
                if (cells.length < columnNames.length) throw new RuntimeException("导入数据有误");
                JSONObject ob = new JSONObject();
                // 获取每一行 一列的数据 拼装实体
                for (int columnIndex = 0;columnIndex < columnNames.length;columnIndex++){
                    ob.put(columnNames[columnIndex],cells[columnIndex].getContents());
                }
                T data = ob.toJavaObject(cls);
                //数据校验
                validationUtil.validate(data);
                result.add(data);
                if (result.size() >= pageSize) return  result;
            }

        }
        try {
            inputStream.close();
        }catch (Exception e){
            e.printStackTrace();
        }

        return result;
    }

    /**
     * @title 简单导出 建议导出数据小于10条时使用
     *
     * @param columns 列名（字段名）
     * @param columnNames 列名（展示列表的中文名）
     * @param fileds 数据集
     * @param outputStream 输出流
     * @throws IOException
     */
    private void simpleDown(String[] columns, String[] columnNames, List<T> fileds, OutputStream outputStream) throws Exception {
        SXSSFWorkbook wb = null;
        SXSSFSheet sheet = null;
        try {
            wb = new SXSSFWorkbook();
        } catch (Exception e) {
            throw new RuntimeException("创建导出流失败");
        }
        // 如果文件超过最大数 则分页
        int count = fileds.size() > sheetSize?(fileds.size() / sheetSize) +1 : 1;
        for (int k = 0 ;k < count; k++){
            sheet = wb.createSheet("sheet"+k);
            sheet.trackAllColumnsForAutoSizing();
            SXSSFRow hearRow = sheet.createRow(0);
            for (int i = 0; i < columnNames.length; i++) {
                hearRow.createCell(i).setCellValue(columnNames[i]);
                //设置自适应
                try {
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    sheet.autoSizeColumn(i);
                    sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            for (int i = 0; i < sheetSize ; i++) {
                SXSSFRow bodyRow = sheet.createRow(i + 1);
                for (int j = 0; j < columnNames.length; j++) {
                    Object data = null;
                    try {
                        data = fileds.get(k * sheetSize + i);
                    }catch (Exception e){
                        continue;
                    }
                    bodyRow.createCell(j).setCellValue(ExcelUtils.getGetMethod(data, columns[j]) + "");
                }
            }

        }

        //关闭源
        try {
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 根据属性，获取get方法
     *
     * @param ob   对象
     * @param name 属性名
     * @return
     * @throws Exception
     */
    private static Object getGetMethod(Object ob, String name) throws Exception {
        Method[] m = ob.getClass().getMethods();
        for (int i = 0; i < m.length; i++) {
            if (("get" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
                return m[i].invoke(ob);
            }
        }
        return null;
    }

    /**
     * 根据属性，获取get方法
     *
     * @param ob   对象
     * @param name 属性名
     * @return
     * @throws Exception
     */
    private static Object getSetMethod(Object ob, String name) throws Exception {
        Method[] m = ob.getClass().getMethods();
        for (int i = 0; i < m.length; i++) {
            if (("set" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
                return m[i].invoke(ob);
            }
        }
        return null;
    }


}
